﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using QLACQUY.DTO;
using System.Data.OleDb;

namespace QLACQUY.DAO
{
    class KhachHangDAO:DataAccess
    {
        public ArrayList layDanhSach()
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select * from KHACH_HANG";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ArrayList list = new ArrayList();
            while (dr.Read())
            {
                KhachHangDTO kh = new KhachHangDTO();
                kh.MaKH = (int)dr["MaKH"];
                kh.TenKH = (string)dr["TenKH"];
                kh.DiaChi = (string)dr["DiaChiKH"];
                kh.DienThoai = (string)dr["DienThoaiKH"];
                kh.NoCu = (double)dr["NoCu"];
                kh.MaSo = (string)dr["MaSo"];
                list.Add(kh);
            }
            dr.Close();
            cn.Close();
            return list;
        }
        public KhachHangDTO layKHTheoTen(string tenKH)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select * from KHACH_HANG where TenKH like '"+tenKH+"'";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            KhachHangDTO kh = null;
            if (dr.Read())
            {
                kh = new KhachHangDTO();              
                kh.MaKH = (int)dr["MaKH"];
                kh.TenKH= (string)dr["TenKH"];
                kh.DienThoai= (string)dr["DienThoaiKH"];
                kh.DiaChi= (string)dr["DiaChiKH"];
                kh.NoCu= (double)dr["NoCu"];
                kh.MaSo = (string)dr["MaSo"];
            }
            dr.Close();
            cn.Close();
            return kh;
        }
        public KhachHangDTO layKHTheoMaSo(string Maso)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select * from KHACH_HANG where MaSo like '" + Maso + "'";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            KhachHangDTO kh = null;
            if (dr.Read())
            {
                kh = new KhachHangDTO();
                kh.MaKH = (int)dr["MaKH"];
                kh.TenKH = (string)dr["TenKH"];
                kh.DienThoai = (string)dr["DienThoaiKH"];
                kh.DiaChi = (string)dr["DiaChiKH"];
                kh.NoCu = (double)dr["NoCu"];
                kh.MaSo = (string)dr["MaSo"];
            }
            dr.Close();
            cn.Close();
            return kh;
        }
        public KhachHangDTO layKHTheoMa(int maKH)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select * from KHACH_HANG where MaKH ="+maKH;
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            KhachHangDTO kh = null;
            if (dr.Read())
            {
                kh = new KhachHangDTO();
                kh.MaKH = (int)dr["MaKH"];
                kh.TenKH = (string)dr["TenKH"];
                kh.DienThoai = (string)dr["DienThoaiKH"];
                kh.DiaChi = (string)dr["DiaChiKH"];
                kh.NoCu = (double)dr["NoCu"];
                kh.MaSo = (string)dr["MaSo"];
            }
            dr.Close();
            cn.Close();
            return kh;
        }
        public bool checkExists(KhachHangDTO dto)
        {
            //kiem tra ton tai bang ten va dien thoai
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select count(*) from KHACH_HANG where TenKH=? and DienThoaiKH=?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenKH", OleDbType.WChar);            
            cmd.Parameters.Add("@DienThoaiKH", OleDbType.WChar);
            
            cmd.Parameters["@TenKH"].Value = dto.TenKH;            
            cmd.Parameters["@DienThoaiKH"].Value = dto.DienThoai;
            
            try
            {
                int k = (int)cmd.ExecuteScalar();
                if (k == 0)
                    return false;
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }         

        }
        public int themKhachHang(KhachHangDTO dto)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "insert into KHACH_HANG(TenKH, DiaChiKH, DienThoaiKH, NoCu, MaSo) values(?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenKH", OleDbType.WChar);
            cmd.Parameters.Add("@DiaChiKH", OleDbType.WChar);
            cmd.Parameters.Add("@DienThoaiKH", OleDbType.WChar);
            cmd.Parameters.Add("@NoCu", OleDbType.Double);
            cmd.Parameters.Add("@MaSo", OleDbType.WChar);            

            cmd.Parameters["@TenKH"].Value = dto.TenKH;
            cmd.Parameters["@DiaChiKH"].Value = dto.DiaChi;
            cmd.Parameters["@DienThoaiKH"].Value = dto.DienThoai;
            cmd.Parameters["@NoCu"].Value = dto.NoCu;
            cmd.Parameters["@MaSo"].Value = dto.MaSo;            
            try
            {
                cmd.ExecuteNonQuery();
                cmd.CommandText = "select @@IDENTITY";
                return (int)cmd.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                return 0;
            }         
        }
        public int capNhatKhachHang(KhachHangDTO dto)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "update KHACH_HANG set DiaChiKH = ?, NoCu = ? where TenKH = ? AND DienThoaiKH = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            
            cmd.Parameters.Add("@DiaChiKH", OleDbType.WChar);            
            cmd.Parameters.Add("@NoCu", OleDbType.Double);
            cmd.Parameters.Add("@TenKH", OleDbType.WChar);
            cmd.Parameters.Add("@DienThoaiKH", OleDbType.WChar);

            cmd.Parameters["@TenKH"].Value = dto.TenKH;
            cmd.Parameters["@DiaChiKH"].Value = dto.DiaChi;
            cmd.Parameters["@DienThoaiKH"].Value = dto.DienThoai;
            cmd.Parameters["@NoCu"].Value = dto.NoCu;
            try
            {
                cmd.ExecuteNonQuery();
                cmd.CommandText = "select * from KHACH_HANG where TenKH='"+dto.TenKH+"' AND DienThoaiKH='"+dto.DienThoai+"'";
                return (int)cmd.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                return 0;
            }         
        }
        public int capNhatKhachHangTheoMa(KhachHangDTO dto)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "update KHACH_HANG set DiaChiKH = ?, NoCu = ?, TenKH = ?, DienThoaiKH = ?, MaSo = ? where MaKH="+dto.MaKH;
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@DiaChiKH", OleDbType.WChar);
            cmd.Parameters.Add("@NoCu", OleDbType.Double);
            cmd.Parameters.Add("@TenKH", OleDbType.WChar);
            cmd.Parameters.Add("@DienThoaiKH", OleDbType.WChar);
            cmd.Parameters.Add("@MaSo", OleDbType.WChar);

            cmd.Parameters["@TenKH"].Value = dto.TenKH;
            cmd.Parameters["@DiaChiKH"].Value = dto.DiaChi;
            cmd.Parameters["@DienThoaiKH"].Value = dto.DienThoai;
            cmd.Parameters["@NoCu"].Value = dto.NoCu;
            cmd.Parameters["@MaSo"].Value = dto.MaSo;

            try
            {
                cmd.ExecuteNonQuery();
                //cmd.CommandText = "select * from KHACH_HANG where TenKH='" + dto.TenKH + "' AND DienThoaiKH='" + dto.DienThoai + "'";
                return 1;// (int)cmd.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                return 0;
            }
        }
    }
}
